package org.jeecg.modules.business.mapper;

import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.business.entity.BusMemberCard;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.jeecg.modules.business.vo.MemberCardStatVo;

/**
 * @Description: bus_member_card
 * @Author: jeecg-boot
 * @Date:   2023-03-10
 * @Version: V1.0
 */
public interface BusMemberCardMapper extends BaseMapper<BusMemberCard> {

    /**
     * 统计
     * @param hotelId
     * @return
     */
    @Select("select count(0) as count,sum(CASE WHEN DATEDIFF(create_time,now())=0 THEN 1 ELSE 0 END) as day_count,\n" +
            "sum(CASE WHEN substring(certificate_no,11,4)=DATE_FORMAT(Now(),'%m%d') THEN 1 ELSE 0 END) as birthday_count,\n" +
            "sum(CASE WHEN substring(certificate_no,11,2)=DATE_FORMAT(Now(),'%m') THEN 1 ELSE 0 END) as month_birthday_count\n" +
            "from bus_member_card where hotel_id = #{hotelId}")
    public MemberCardStatVo stat(String hotelId);
}
